<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<!-- Purpose: XSLT to convert SQL schema XML into DROP/CREATE of DB objects.
	 Known limitations: no support for indexed views or the index fillfactor. 
	 Author: Lindsey Lewis, 08-26-2005 -->
	<xsl:output method="xml" omit-xml-declaration="yes"/>
	
	<xsl:template match="/">
		<xsl:apply-templates/>
	</xsl:template>
	
<!-- template to output ALL SQL statement types -->
	<xsl:template match="DataBase_Schema">
		<xsl:apply-templates select="Database"/>
<!-- generate defaults, rules and UDDTs: in that order -->
		<xsl:apply-templates select="DEFAULT"/>
		<xsl:apply-templates select="RULE"/>
		<xsl:apply-templates select="UDDT"/>
	    <xsl:apply-templates select="TABLE"/>
<!-- pulled the following template out of the TABLE template so that the constraints would get created after all the tables were -->
		<xsl:apply-templates select="TABLE/TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="ADD"/>
	    <xsl:apply-templates select="VIEW"/>
	    <xsl:apply-templates select="FUNC"/>
	    <xsl:apply-templates select="SPROC"/>
	    <xsl:apply-templates select="TRIGGER"/>

DECLARE @ERR int

SET @ERR = @@ERROR
IF @@TRANCOUNT > 0
BEGIN
	IF @ERR > 0
		ROLLBACK TRANSACTION
	ELSE
		COMMIT TRANSACTION T1
END
SET XACT_ABORT OFF
	</xsl:template>
	
<!-- template to output comment header -->
	<xsl:template match="Database">
-- DB Name: <xsl:value-of select="Name"/>
-- Output Date: <xsl:value-of select="Date"/>
-- Output Time: <xsl:value-of select="Time"/>
-- AutoGenerated SQL: using the SQL Schema Tool.

/*
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time 
error, the entire transaction is terminated and rolled back. When OFF, only the 
Transact-SQL statement that raised the error is rolled back and the transaction 
continues processing. Compile errors, such as syntax errors, are not affected by 
SET XACT_ABORT.
*/

SET QUOTED_IDENTIFIER ON
SET XACT_ABORT ON
BEGIN TRANSACTION T1 WITH MARK 'Apply Create SQL'
	</xsl:template>

<!-- template to output defaults -->
	<xsl:template match="DEFAULT">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="DEFAULT_NAME"/>]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
BEGIN
	DROP DEFAULT [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="DEFAULT_NAME"/>]
END
GO

<xsl:for-each select="DEFAULT_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO
	</xsl:template>

<!-- template to output rules -->
	<xsl:template match="RULE">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="RULE_NAME"/>]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
	DROP RULE [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="RULE_NAME"/>]
END
GO

<xsl:for-each select="RULE_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO
	</xsl:template>

<!-- template to output UDDTs -->
	<xsl:template match="UDDT">
if exists (select * from dbo.systypes where name = N'<xsl:value-of select="UDDT_NAME"/>')
BEGIN
	EXEC sp_droptype N'<xsl:value-of select="UDDT_NAME"/>'
END
GO
	
<xsl:for-each select="UDDT_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO
	</xsl:template>
	
<!-- template to output Create Table statement -->
	<xsl:template match="TABLE">
		<xsl:variable name="_towner"><xsl:value-of select="TABLE_OWNER"/></xsl:variable> 
		<xsl:apply-templates select="TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="DROP"/>
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	DROP TABLE [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
END	
GO

IF (SELECT FILEGROUP_ID('<xsl:value-of select="TABLE_FILEGROUP/groupname"/>')) IS NOT NULL
	CREATE TABLE [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	( 	<xsl:for-each select="COLUMN[isComputed=0]"><xsl:call-template name="_CreateColumn"/></xsl:for-each>
	)   <xsl:apply-templates select="TABLE_FILEGROUP"/> <xsl:choose>
		<xsl:when test="COLUMN[Base_Type='image']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> <xsl:when test="COLUMN[Base_Type='ntext']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> <xsl:when test="COLUMN[Base_Type='text']"> TEXTIMAGE_ON [<xsl:value-of select="TABLE_FILEGROUP/groupname"/>] </xsl:when> </xsl:choose>
ELSE
	RAISERROR ('You will have to manually add the filegroup <xsl:value-of select="TABLE_FILEGROUP/groupname"/> to the SQL DB or edit this SQL script to set the FileGroups to PRIMARY', 16, 1)
RETURN
GO

		<xsl:for-each select="COLUMN[isComputed=0 and isRowGuidCol=0 and contains(Base_Type,'text')!=1 and ((Type!=Base_Type and string-length(Collation)>0) or (isNullable=0 and string-length(Default_Name)=0))]">
<xsl:if test="position()=1">--2nd pass thru after add/alter table columns to handle those columns having UDDTs or Not Nulls </xsl:if>
			<xsl:call-template name="_ColumnUserDefines"/>
<xsl:if test="position()=last()"><xsl:text>
GO

</xsl:text>
</xsl:if>	
		</xsl:for-each>
		<xsl:for-each select="COLUMN[isComputed=1]">
			<xsl:call-template name="_CalcColumn"/>
<xsl:if test="position()=last()"><xsl:text>
GO

</xsl:text>
</xsl:if>			
		</xsl:for-each>
		<xsl:for-each select="COLUMN[string-length(Rule_Name)>0]">
			<xsl:call-template name="_bindColumnRule"/>
		</xsl:for-each>

		<xsl:apply-templates select="TABLE_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]"/>
		<xsl:apply-templates select="TABLE_INDEX[string-length(index_name)>0]" mode="PK"/>
		<xsl:apply-templates select="TABLE_INDEX[string-length(index_name)>0]" mode="NDX"/>
	</xsl:template>
	
<!-- template to output non-calculated CREATE TABLE Column -->
	<xsl:template name="_CreateColumn" match="COLUMN[isComputed=0]">
	    <xsl:variable name="_isIdent" select="isIdentity"/>
	    <xsl:variable name="_type" select="Base_Type"/>
		[<xsl:value-of select="Column_Name"/>] [<xsl:value-of select="$_type"/>] <xsl:if test="contains($_type, 'char')"> (<xsl:value-of select="Length"/>) </xsl:if> <xsl:if test="$_isIdent=1"> Identity (<xsl:value-of select="Seed"/> ,<xsl:value-of select="Increment"/>)</xsl:if><xsl:if test="NotforRepl=1"> NOT FOR REPLICATION</xsl:if><xsl:if test="string-length(Collation) > 0"> COLLATE </xsl:if> <xsl:value-of select="Collation"/><xsl:if test="isNullable=0"> NOT</xsl:if> NULL<xsl:if test="isRowGuidCol=1"> ROWGUIDCOL</xsl:if><xsl:if test="string-length(Default_Name)>0"> DEFAULT <xsl:value-of select="Default_Value"/></xsl:if><xsl:if test="position()!=last()">,</xsl:if>
	</xsl:template>
	
<!-- template to output calculated Table Column -->
	<xsl:template name="_CalcColumn" match="COLUMN[isComputed=1]">
		<xsl:if test="position()=1">
	-- add calculated columns to tables	
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	ADD </xsl:if>[<xsl:value-of select="Column_Name"/>] AS <xsl:value-of select="Calc_Text"/><xsl:if test="position()!=last()">,</xsl:if>
	</xsl:template>
	
<!-- template to bind existing rule to Table Column -->
	<xsl:template name="_bindColumnRule" match="COLUMN[string-length(Rule_Name)>0]">
		<xsl:if test="position()=1">
-- Bind rule to column
		</xsl:if>
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="Rule_Owner"/>].[<xsl:value-of select="Rule_Name"/>]') and OBJECTPROPERTY(id, N'IsRule') = 1)
BEGIN
	EXEC sp_bindrule <xsl:value-of select="Rule_Name"/>, '[<xsl:value-of select="TABLE_NAME"/>].[<xsl:value-of select="Column_Name"/>]'
END
ELSE
	RAISERROR ('Cannot bind rule to column: [<xsl:value-of select="Column_Name"/>].  Rule: <xsl:value-of select="Rule_Name"/> does not exist.', 16, 1)
GO
	</xsl:template>
	
<!-- template to for 2nd pass to add user defined defaults to table columns -->
	<xsl:template name="_ColumnUserDefines" match="COLUMN[isComputed=0 and isRowGuidCol=0 and contains(Base_Type,'text')!=1 and ((Type!=Base_Type and string-length(Collation)>0) or (isNullable=0 and string-length(Default_Name)=0))]">
	ALTER TABLE [<xsl:value-of select="../TABLE_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]
	ALTER COLUMN [<xsl:value-of select="Column_Name"/>] [<xsl:value-of select="Type"/>] <xsl:if test="contains(Base_Type,'char') and Base_Type=Type"> (<xsl:value-of select="Length"/>) </xsl:if><xsl:if test="isNullable=0">NOT NULL</xsl:if><xsl:text>
</xsl:text>
	</xsl:template>
	
<!-- template to output Table Filegroup -->
	<xsl:template match="TABLE_FILEGROUP">ON [<xsl:value-of select="groupname"/>] </xsl:template>

<!-- template calls template to output Index Table Constraints statements -->
	<xsl:template match="TABLE_INDEX[string-length(index_name)>0]" mode="PK">
		<xsl:call-template name="PK_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
	</xsl:template>

<!-- template calls template to output create index statements -->
	<xsl:template match="TABLE_INDEX[string-length(index_name)>0]" mode="NDX">
		<xsl:call-template name="T_INDEX">
			<xsl:with-param name="_towner"><xsl:value-of select="../TABLE_OWNER"/></xsl:with-param>
		</xsl:call-template>
	</xsl:template>

<!-- template to output Index Table Constraints statements -->
	<xsl:template  name="PK_INDEX">
		<xsl:param name="_towner"> </xsl:param>
		<xsl:variable name="_tname" select="TABLE_NAME"/>
		<xsl:variable name="_desc" select="index_description"/>
		<xsl:variable name="_name" select="index_name"/>
		<xsl:variable name="_keys" select="index_keys"/>
		<xsl:variable name="_gpname" select="substring-after($_desc,'located on ')"/>
		<xsl:variable name="_select"> (SELECT COLUMNPROPERTY( OBJECT_ID(N'<xsl:value-of select="$_tname"/>'), N'</xsl:variable>
		<xsl:variable name="_end1">', 'AllowsNull')) IS NOT NULL  AND </xsl:variable>
		<xsl:variable name="_end2">', 'AllowsNull')) IS NOT NULL  </xsl:variable>
		<xsl:variable name="_part0">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_keys"/>
				<xsl:with-param name="substringIn">(-)</xsl:with-param>
				<xsl:with-param name="substringOut"> DESC</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part1">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="concat('[',$_part0,']')"/>
				<xsl:with-param name="substringIn">, </xsl:with-param>
				<xsl:with-param name="substringOut">], [</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part2">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part1"/>
				<xsl:with-param name="substringIn">[</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_select"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part3">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part2"/>
				<xsl:with-param name="substringIn">],</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_end1"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part4">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_part3"/>
				<xsl:with-param name="substringIn">]</xsl:with-param>
				<xsl:with-param name="substringOut" select="$_end2"/>
			</xsl:call-template> 
		</xsl:variable>
		<xsl:if test="contains($_desc, 'primary key') or contains($_desc, 'unique key')">
IF ( <xsl:value-of select="$_part4"/> ) and EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND (SELECT FILEGROUP_ID('<xsl:value-of select="$_gpname"/>')) IS NOT NULL
BEGIN
	ALTER TABLE [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>] ADD 
	CONSTRAINT [<xsl:value-of select="$_name"/>] <xsl:if test="contains($_desc, 'primary key')">PRIMARY KEY</xsl:if><xsl:if test="contains($_desc, 'unique key')">UNIQUE</xsl:if> <xsl:choose><xsl:when test="contains($_desc, 'nonclustered')"> NONCLUSTERED </xsl:when><xsl:when test="contains($_desc, 'clustered')"> CLUSTERED </xsl:when></xsl:choose>
	(
		<xsl:value-of select="$_part1"/>
	)  ON [<xsl:value-of select="$_gpname"/>] 
END
ELSE
	RAISERROR ('Constraint column(s): <xsl:value-of select="$_keys"/> do(es) not exist, or Table or File Group does not exist.  Check for other errors.', 16, 1)
GO
		</xsl:if>
	</xsl:template>

<!-- template to output create index statements -->
	<xsl:template  name="T_INDEX">
		<xsl:param name="_towner"> </xsl:param>
		<xsl:variable name="_tname" select="TABLE_NAME"/>
		<xsl:variable name="_desc" select="index_description"/>
		<xsl:variable name="_name" select="index_name"/>
		<xsl:variable name="_keys" select="index_keys"/>
		<xsl:variable name="_gpname" select="substring-after($_desc,'located on ')"/>
		<xsl:variable name="_part0">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="$_keys"/>
				<xsl:with-param name="substringIn">(-)</xsl:with-param>
				<xsl:with-param name="substringOut"> DESC</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="_part1">
			<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="concat('[',$_part0,']')"/>
				<xsl:with-param name="substringIn">, </xsl:with-param>
				<xsl:with-param name="substringOut">], [</xsl:with-param>
			</xsl:call-template>
		</xsl:variable>
		<xsl:if test="contains($_desc, 'primary key') != 1 and contains($_desc, 'unique key') != 1">
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) AND (SELECT FILEGROUP_ID('<xsl:value-of select="$_gpname"/>')) IS NOT NULL
BEGIN
	CREATE <xsl:if test="contains($_desc, 'nonclustered, unique located')">UNIQUE</xsl:if> INDEX [<xsl:value-of select="$_name"/>] ON [<xsl:value-of select="$_towner"/>].[<xsl:value-of select="$_tname"/>](<xsl:value-of select="$_part1"/>) ON [<xsl:value-of select="$_gpname"/>]
END
ELSE
	RAISERROR ('Table or File Group does not exist.  Check for other errors that caused the table to not be created.', 16, 1)
	
GO
		</xsl:if>
	</xsl:template>

<!-- template to output (CK) checked and default table constraints -->
	<xsl:template match="TABLE_CONSTRAINTS[string-length(CONSTRAINT_NAME)>0]">
IF (SELECT OBJECT_ID(N'<xsl:value-of select="CONSTRAINT_NAME"/>')) IS NULL AND EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	ALTER TABLE [<xsl:value-of select="CONSTRAINT_OWNER"/>].[<xsl:value-of select="TABLE_NAME"/>] ADD 
	CONSTRAINT  [<xsl:value-of select="CONSTRAINT_NAME"/>] 
	<xsl:if test="substring(CONSTRAINT_TYPE,1,1)='C'"> CHECK </xsl:if><xsl:if test="substring(CONSTRAINT_TYPE,1,1)='D'"> DEFAULT </xsl:if> <xsl:value-of disable-output-escaping="yes" select="CONSTRAINT_CLAUSE"/><xsl:if test="substring(CONSTRAINT_TYPE,1,1)='D' and string-length(COLUMN_NAME)>0"> FOR <xsl:value-of select="COLUMN_NAME"/></xsl:if>
END
	ELSE
		RAISERROR ('Check Constraint <xsl:value-of select="CONSTRAINT_NAME"/> already exist, or the Table does not exist. Check for other errors.', 16, 1)
GO
	</xsl:template>

<!-- template to output DROP constraint statements for (FK) referenced tables -->
	<xsl:template match="TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="DROP">
IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[<xsl:value-of select="Constraint"/>]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) AND EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>]  
	DROP CONSTRAINT <xsl:value-of select="Constraint"/>
END
GO
	</xsl:template>

<!-- template to output (FK) referenced table constraints -->
	<xsl:template match="TABLE_REFERENCE[string-length(cKeyCol1)>0 and PK_Table=TABLE_NAME]" mode="ADD">
IF object_id(N'<xsl:value-of select="FK_Table"/>') IS NOT NULL AND (SELECT COLUMNPROPERTY( object_id(N'<xsl:value-of select="PK_Table"/>'), N'<xsl:value-of select="cRefCol1"/>', 'AllowsNull') ) IS NOT NULL
BEGIN
	ALTER TABLE [<xsl:value-of select="FK_Table_Owner"/>].[<xsl:value-of select="FK_Table"/>] ADD 
	CONSTRAINT [<xsl:value-of select="Constraint"/>] FOREIGN KEY 
	(	<xsl:for-each select="descendant::*[contains(local-name(),'cKeyCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	) REFERENCES [<xsl:value-of select="PK_Table_Owner"/>].[<xsl:value-of select="PK_Table"/>] ( <xsl:for-each select="descendant::*[contains(local-name(),'cRefCol')]">
		[<xsl:value-of select="."/>]<xsl:if test="position()!=last()">,</xsl:if></xsl:for-each>
	)
END
ELSE
	RAISERROR ('Constraint referenced table: <xsl:value-of select="FK_Table"/>, or the related column: <xsl:value-of select="cRefCol1"/> does not exist', 16, 1)
GO
	</xsl:template>

<!-- template to output View statements -->
	<xsl:template match="VIEW">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="VIEW_NAME"/>]') and OBJECTPROPERTY(id, N'IsView') = 1)
BEGIN
	DROP VIEW [<xsl:value-of select="TABLE_OWNER"/>].[<xsl:value-of select="VIEW_NAME"/>]
END
GO

<xsl:for-each select="VIEW_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required view object dependency is missing, unable to create view', 16, 1)
	END
</xsl:if> </xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

<xsl:for-each select="CREATE_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output stored procedure statements -->
	<xsl:template match="SPROC">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="SPROC_NAME"/>]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="SPROC_NAME"/>]
END
GO

<xsl:for-each select="SPROC_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required procedure object dependency is missing, unable to create procedure', 16, 1)
	END
</xsl:if> </xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

<xsl:for-each select="SPROC_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output function statements -->
	<xsl:template match="FUNC">
if exists (select * from dbo.sysobjects where id = object_id(N'[<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="FUNC_NAME"/>]') and xtype in (N'FN', N'IF', N'TF'))
BEGIN
	DROP FUNCTION [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="FUNC_NAME"/>]
END
GO

<xsl:for-each select="FUNC_DEPENDS[string-length(depname)>0]"><xsl:if test="position()=1">if (</xsl:if> OBJECT_ID(N'<xsl:value-of select="depname"/>') IS NULL <xsl:if test="position()!=last()">OR</xsl:if> <xsl:if test="position()=last()"> )
	BEGIN
		RAISERROR ('Required function object dependency is missing, unable to create function', 16, 1)
	END
</xsl:if> </xsl:for-each> 
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

<xsl:for-each select="FUNC_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF
GO
	</xsl:template>

<!-- template to output Triggers -->
	<xsl:template match="TRIGGER">
if exists (select * from dbo.sysobjects where [name] = '<xsl:value-of select="TRIGGER_NAME"/>' and [type] = 'TR')
BEGIN
	DROP TRIGGER [<xsl:value-of select="USER_NAME"/>].[<xsl:value-of select="TRIGGER_NAME"/>]
END
GO
	
SET QUOTED_IDENTIFIER, ANSI_NULLS ON 
GO

<xsl:for-each select="TRIGGER_TEXT">
	<xsl:call-template name="dblquot-replace">
		<xsl:with-param name="string">
			<xsl:value-of disable-output-escaping="yes" select="Text"/>
		</xsl:with-param>
		<xsl:with-param name="counter" select="0"/>
	</xsl:call-template>
</xsl:for-each>
GO

SET QUOTED_IDENTIFIER, ANSI_NULLS OFF 
GO
	</xsl:template>

<!-- replace all occurences of the character 'dblquote'
	by the character '[' and ']' in the string 'string'.-->
	<xsl:template name="dblquot-replace" >
		<xsl:param name="string"/>
		<xsl:param name="counter"/>		
		<xsl:variable name="lt">[</xsl:variable>
		<xsl:variable name="rt">]</xsl:variable>
		<xsl:variable name="dq">"</xsl:variable>
		<xsl:variable name="use">
			<xsl:choose>
				<xsl:when test="($counter mod 2)=0">
					<xsl:value-of select="$lt"/>
				</xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="$rt"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:choose>
			<xsl:when test="contains($string, $dq)">
				<xsl:value-of select="substring-before($string, $dq)"/>
				<xsl:value-of select="$use"/>
				<xsl:call-template name="dblquot-replace">
					<xsl:with-param name="string" select="substring-after($string, $dq)"/>
					<xsl:with-param name="counter" select="$counter + 1"/>
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$string"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

<!-- functional equivalent of substring method -->
	<xsl:template name="SubstringReplace">
		<xsl:param name="stringIn" /> 
		<xsl:param name="substringIn" /> 
		<xsl:param name="substringOut" /> 
		<xsl:choose>
			<xsl:when test="contains($stringIn,$substringIn)">
				<xsl:value-of select="concat(substring-before($stringIn,$substringIn),$substringOut)" /> 
				<xsl:call-template name="SubstringReplace">
				<xsl:with-param name="stringIn" select="substring-after($stringIn,$substringIn)" /> 
				<xsl:with-param name="substringIn" select="$substringIn" /> 
				<xsl:with-param name="substringOut" select="$substringOut" /> 
				</xsl:call-template>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$stringIn" /> 
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>

</xsl:stylesheet>